package ru.nsu.fit.shared.data_base_manager;

import ru.nsu.fit.shared.config_manager.ConfigManager;
import ru.nsu.fit.shared.config_manager.Globals;
import ru.nsu.fit.shared.questions_request.QuestionDB;
import ru.nsu.fit.shared.questions_request.QuestionOption;
import ru.nsu.fit.shared.questions_request.QuestionsGetterInputForDB;
import ru.nsu.fit.shared.questions_request.QuestionsGetterOutputFromDB;
import ru.nsu.fit.shared.questions_stat_request.QuestionStatDB;
import ru.nsu.fit.shared.questions_stat_request.QuestionsStatGetterInputForDB;
import ru.nsu.fit.shared.questions_stat_request.QuestionsStatGetterOutputFromDB;
import ru.nsu.fit.ws.get_user_stat.UserStatOutput;

import java.sql.*;
import java.util.*;

/**
 * This class provides access to data base.
 * @author Vlad Timofeev, Sergey Kozlov
 */
public class DatabaseManager {
    private static ConfigManager configManager = ConfigManager.getInstance();

    public static final String CREATE_QUESTION_AND_ANSWER_MAPPING = "INSERT question_and_answer (`question_id`, `answer_id`) VALUES(?,?)";
    public static final String CREATE_ANSWER = "INSERT answer (`answer`) VALUES(?)";
    public static final String INSERT_QUESTION = "INSERT question (`text`,`author_id`, `start_date`, `finish_date`, `lifetime`) VALUES(?,?,?,?,?)";

    // Inserts value to result table
    public static final String INSERT_RESULT = "INSERT result (`user_id`, `question_id`, `income`) VALUES(?,?,?)";

    public static final String GET_ANSWER_ID = "SELECT t1.id" +
            " FROM answer AS t1 INNER JOIN question_and_answer AS t2 ON t1.id = t2.answer_id" +
            " WHERE t1.answer = ? AND t2.question_id = ?";

    // Returns answer ids by question id
    public static final String GET_ANSWER_IDS = "SELECT t1.id" +
            " FROM answer AS t1 INNER JOIN question_and_answer AS t2 ON t1.id = t2.answer_id" +
            " WHERE t2.question_id = ?";

    // Returns count same answer count by answer
    public static final String GET_ANSWER_COUNT = "SELECT count(*) " +
            " FROM user_and_answer AS t1 " +
            " WHERE t1.answer_id = ?";

    // Returns user ids by answer id
    public static final String GET_USER_IDS_BY_ANSWER_ID = "SELECT t1.user_id" +
            " FROM user_and_answer AS t1 " +
            " WHERE t1.answer_id = ?";

    // Returns user rating by id
    public static final String GET_USER_RATING_BY_ID = "SELECT t1.rating" +
            " FROM user AS t1 " +
            " WHERE t1.id = ?";

    // Returns question ids by state and finish date
    public static final String GET_QUESTIONS_BY_STATE_AND_FINISH_DATE = "SELECT t1.id" +
            " FROM question AS t1 " +
            " WHERE t1.state = ? and t1.finish_date <= ?";

    // Returns question ids by state
    public static final String GET_QUESTIONS_BY_STATE = "SELECT t1.id" +
            " FROM question AS t1 " +
            " WHERE t1.state = ?";

    // Returns user id of JOE
    public static final String GET_USER_JOE_ID = "SELECT t1.id" +
            " FROM user AS t1 " +
            " WHERE t1.login = '" + Globals.JOE_LOGIN + "'";

    // Updates user rating by id
    public static final String UPDATE_USER_RATING_BY_ID = "UPDATE user SET rating = ? WHERE id = ?";

    // Get author id by question id
    public static final String GET_AUTHOR_ID_BY_QUESTION_ID = "SELECT t1.author_id " +
            " FROM question AS t1 " +
            " WHERE t1.id = ? ";

    // Get finish date by question id
    public static final String GET_FINISH_DATE_BY_QUESTION_ID = "SELECT t1.finish_date " +
            " FROM question AS t1 " +
            " WHERE t1.id = ? ";

    // Updates question state by id
    public static final String UPDATE_QUESTION_STATE_BY_ID = "UPDATE question SET state = ? WHERE id = ?";

    // Updates question lifetime by id
    public static final String UPDATE_QUESTION_LIFETIME_BY_ID = "UPDATE question SET lifetime = ? WHERE id = ?";

    public static final String GET_ANSWER_ID_AND_QUESTION_ID = "SELECT id " +
            " FROM question_and_answer " +
            " WHERE answer_id = ? AND question_id = ?";
    public static final String INSERT_ANSWER = "INSERT user_and_answer (`user_id`, `answer_id`) "+
            "VALUES(?,?)";

    public static final String DROP_TEMPRORARY_QUESSTIONS =
            "DROP TABLE IF EXISTS ask_joe_database.IDS; ";
    public static final String CREATE_TEMPRORARY_QUESSTIONS =
            "CREATE TEMPORARY TABLE ask_joe_database.IDS SELECT * FROM ask_joe_database.question " +
            "WHERE author_id != ? AND state = 0 ORDER BY rating,id LIMIT ?,?; ";

    public static final String GET_QUESTIONS_WITHOUT_LOCATION =
            "SELECT t1.id, t1.text, t1.lifetime, t3.answer, t2.question_id, t2.answer_id FROM "+
            "(ask_joe_database.IDS as t1 INNER JOIN ask_joe_database.question_and_answer "+
            "as t2 ON t1.id = t2.question_id) "+
            "INNER JOIN ask_joe_database.answer as t3 ON t2.answer_id = t3.id; ";

    //TODO Not used and invalid. Need to be set up in GET_QUESTIONS_WITHOUT_LOCATION style
//    public static final String GET_QUESTIONS_WITH_LOCATION =
//            "SELECT t1.id, t1.text, t1.lifetime, t3.answer, t2.question_id, t2.answer_id FROM "+
//            "(ask_joe_database.question as t1 INNER JOIN ask_joe_database.question_and_answer " +
//            "as t2 ON t1.id = t2.question_id) "+
//            "INNER JOIN ask_joe_database.answer as t3 ON t2.answer_id = t3.id "+
//            "WHERE author_id != ? AND "+
//            "2*6371/PI()*(ACOS(SIN(latitude*PI()/180)*SIN(?*PI()/180)+COS(latitude*PI()/180)*COS" +
//                    "(?*PI()/180)*COS((longitude-?)*PI()/180))) < ? "+
//            "ORDER BY rating,id "+
//            "LIMIT ?,?";

    public static final String GET_QUESTION_STAT =
            "SELECT t1.author_id, t1.id, t1.text, t1.lifetime, t3.answer, t2.question_id, " +
                    "t2.answer_id FROM "+
                    "(ask_joe_database.question as t1 INNER JOIN ask_joe_database.question_and_answer " +
                    "as t2 ON t1.id = t2.question_id) "+
                    "INNER JOIN ask_joe_database.answer as t3 ON t2.answer_id = t3.id "+
                    "WHERE t2.question_id = ? "+
                    "ORDER BY rating,id ";
    public static final String GET_QUESTION_INCOME =
            "SELECT income FROM ask_joe_database.result "+
                    "WHERE question_id = ? AND user_id = ?";
    public static final String GET_USER_ANSWER =
            "SELECT * FROM "+
                    "ask_joe_database.question as t1 INNER JOIN ask_joe_database.question_and_answer " +
                    "as t2 ON (t1.id = ?) AND (t1.id = t2.question_id) "+
                    "INNER JOIN ask_joe_database.user_and_answer as t3 ON " +
                    "(t3.user_id = ?) AND (t3.answer_id = t2.answer_id)";

    private static Connection createConnection() throws SQLException {

        try {
            Class.forName(configManager.getProperty(Globals.DATA_BASE_DRIVER_ATTR_NAME)).newInstance();
            return DriverManager.getConnection(configManager.getProperty(Globals
                    .DATA_BASE_URL_ATTR_NAME) + configManager.getProperty(Globals.DATA_BASE_NAME_ATTR_NAME),
                    configManager.getProperty(Globals.DATA_BASE_USER_NAME_ATTR_NAME),
                    configManager.getProperty(Globals.DATA_BASE_PASSWORD_ATTR_NAME));

        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("Error while creating connection. This can be because of" +
                    "wrong /data/config.ini file");
        }
    }

    private static void closeConnection(Connection connection) throws SQLException {

        if (!connection.getAutoCommit()) {
            connection.commit();
        }
        connection.close();
    }

    public static boolean isLoginExistent(String login) throws SQLException {
        Connection connection = createConnection();

        String selectString = "SELECT login FROM user WHERE login = ?";
        PreparedStatement prepStmt = connection.prepareStatement(selectString);
        prepStmt.setString(1, login);
        ResultSet res = prepStmt.executeQuery();

        boolean isNext = res.next();
        closeConnection(connection);
        //If there is such login in DB
        return isNext;
    }

    public static void addUser(String login, String password) throws SQLException {
    	Connection connection = createConnection();

        String sqlString = "INSERT user (`login`,`pass`) VALUES(?,?)";

        PreparedStatement prepStmt = connection.prepareStatement(sqlString);
        prepStmt.setString(1, login);
        prepStmt.setString(2, password);
        prepStmt.executeUpdate();

        closeConnection(connection);
    }

    /**
     * @param userSessionId User session
     * @return negative if there is no such userSessionId in DB; otherwise userID
     * @throws SQLException on DB failure
     */
    public static int getUserId(long userSessionId) throws SQLException {
    	Connection connection = createConnection();
        
        String sqlString = "SELECT id FROM user WHERE user_session_id = ?";
        PreparedStatement prepStmt = connection.prepareStatement(sqlString);
        prepStmt.setLong(1, userSessionId);

        ResultSet res = prepStmt.executeQuery();

        if (res.next()) {
            int id = res.getInt("id");
            closeConnection(connection);
            return id;
        }
        else {
            closeConnection(connection);
            return -1;
        }
    }

    public static long authenticate(final String login, final String pass) throws SQLException {
    	Connection connection = createConnection();
        try{
            // FEX ME need lock row in table only
            PreparedStatement statement = connection.prepareStatement("LOCK TABLE user WRITE;");
            statement.execute();

            statement = connection.prepareStatement("SELECT id FROM user WHERE login = ? AND pass = ?");
            statement.setString(1, login);
            statement.setString(2, pass);
            ResultSet result = statement.executeQuery();

            if(!result.next()) {
                throw new UnregisteredLoginAndPasswordException(login);
            }

            // FIX ME, need generate uniq user session id
            int id = result.getInt("id");
            long userSessionId = (id + login + pass).hashCode();

            statement = connection.prepareStatement("UPDATE user SET user_session_id = ? WHERE login = ? AND pass = ?");
            statement.setLong(1, userSessionId);
            statement.setString(2, login);
            statement.setString(3, pass);
            statement.executeUpdate();

            return userSessionId;
        }
        finally {
            closeConnection(connection);
        }
    }

    public static UserStatOutput getUserStat(final long userSessionId) throws SQLException {
        final Connection connection = createConnection();
        try{
            final int rating = getRating(userSessionId, connection);
            final int ratingPosition = getRatingPosition(rating, connection);
            final int userId = userSessionIdToUserId(userSessionId, connection);
            final int[] askedQuestionIds = getAskedQuestionIds(userId, connection);
            final int[] answeredQuestionIds = getAnsweredQuestionIds(userId, connection);
            final UserStatOutput output = new UserStatOutput();
            output.setUserRating(rating);
            output.setUserRatingPosition(ratingPosition);
            output.setAskedQuestionIds(askedQuestionIds);
            output.setAnsweredQuestionIds(answeredQuestionIds);
            return output;
        }
        finally {
            closeConnection(connection);
        }
    }

    private static int getRating(final long userSessionId, final Connection connection) throws SQLException {
        final PreparedStatement statement =
                connection.prepareStatement("SELECT rating FROM user WHERE user_session_id = ?");
        statement.setLong(1, userSessionId);
        final ResultSet result = statement.executeQuery();
        if(!result.next()){
            throw new NoSuchUserSessionIdException(userSessionId);
        }
        return result.getInt("rating");
    }

    private static int getRatingPosition(final int rating, final Connection connection) throws SQLException {
        final PreparedStatement statement =
                connection.prepareStatement("SELECT COUNT(*) FROM user WHERE rating > ?");
        statement.setInt(1, rating);
        final ResultSet result = statement.executeQuery();
        result.next();
        return result.getInt(1) + 1;
    }

    private static int userSessionIdToUserId(final long userSessionId, final Connection connection)
            throws SQLException {
        final PreparedStatement statement =
                connection.prepareStatement("SELECT id FROM user WHERE user_session_id = ?");
        statement.setLong(1, userSessionId);
        final ResultSet result = statement.executeQuery();
        if(!result.next()){
            throw new NoSuchUserSessionIdException(userSessionId);
        }
        return result.getInt("id");
    }

    private static int[] getAskedQuestionIds(final int userId, final Connection connection) throws SQLException {
        final PreparedStatement statement = connection.prepareStatement("SELECT id FROM question WHERE author_id = ?");
        statement.setInt(1, userId);
        final ResultSet result = statement.executeQuery();
        return getIntColumn(result, "id");
    }

    private static int[] getAnsweredQuestionIds(final int userId, final Connection connection) throws SQLException {
        final PreparedStatement statement =
                connection.prepareStatement
                        ("SELECT question_id FROM user_and_answer INNER JOIN question_and_answer ON user_and_answer.answer_id = question_and_answer.answer_id WHERE user_id = ? GROUP BY question_id");
        statement.setInt(1, userId);
        final ResultSet result = statement.executeQuery();
        return getIntColumn(result, "question_id");
    }

    private static int[] getIntColumn(final ResultSet resultSet, final String columnName) throws SQLException {
        final List<Integer> list = new LinkedList<Integer>();
        while (resultSet.next()){
            list.add(resultSet.getInt(columnName));
        }
        final int[] column = new int[list.size()];
        for (int i = 0; i < column.length; i++){
            column[i] = list.get(i);
        }
        return column;
    }

    public static int createQuestion(int userID, String questionText, String[] questionOptions) throws SQLException {
        Connection connection = createConnection();
        connection.setAutoCommit(false);

        try {
            // insert question to table
            long lifetime = new Long(configManager.getProperty(Globals.DEFAULT_LIFETIME_ATTR_NAME));
            long start_time = System.currentTimeMillis();
            long finish_time = start_time + lifetime;

            PreparedStatement prepStmt = connection.prepareStatement(INSERT_QUESTION, Statement.RETURN_GENERATED_KEYS);
            prepStmt.setString(1, questionText);
            prepStmt.setInt(2, userID);
            prepStmt.setLong(3, start_time);
            prepStmt.setLong(4, finish_time);
            prepStmt.setLong(5, lifetime);
            int rowsAffected = prepStmt.executeUpdate();
            if (rowsAffected < 1) {
                throw new SQLException("Error while inserting question text");
            }
            int questionId = getGeneratedIdFromStatement(prepStmt);

            // insert options of question
            if (questionOptions == null || questionOptions.length == 0) {
                createDefaultOptionsForQuestion(connection, questionId);
            } else {
                createCustomOptionsForQuestion(connection, questionId, questionOptions);
            }
            connection.commit();
            closeConnection(connection);
            return questionId;
        }
        catch (SQLException e) {
            connection.rollback();
            closeConnection(connection);
            throw e;
        }
    }

    private static int getGeneratedIdFromStatement(PreparedStatement prepStmt) throws SQLException {
        ResultSet genKeys = prepStmt.getGeneratedKeys();
        genKeys.next();
        return genKeys.getInt(1);
    }

    private static void createDefaultOptionsForQuestion(Connection connection, int questionId) throws SQLException {
        insertQuestionOption(connection, Globals.YES_ANSWER, questionId);
        insertQuestionOption(connection, Globals.NO_ANSWER, questionId);
        insertQuestionOption(connection, Globals.DISLIKE_ANSWER, questionId);
    }

    private static void createCustomOptionsForQuestion(Connection connection, 
        int questionId, String[] questionOptions) throws SQLException {

        for (String option : questionOptions) {
            insertQuestionOption(connection, option, questionId);
        }
        insertQuestionOption(connection, Globals.DISLIKE_ANSWER, questionId);
    }

    private static void insertQuestionOption(Connection connection, String answer, int questionId) throws SQLException {

        PreparedStatement prepStmt = connection.prepareStatement(CREATE_ANSWER,
                Statement.RETURN_GENERATED_KEYS);
        prepStmt.setString(1, answer);
        int rowsAffected = prepStmt.executeUpdate();
        if (rowsAffected < 1) {
            throw new SQLException("Error while creating question option");
        }

        int answerId = getGeneratedIdFromStatement(prepStmt);

        prepStmt = connection.prepareStatement(CREATE_QUESTION_AND_ANSWER_MAPPING,
                Statement.RETURN_GENERATED_KEYS);
        prepStmt.setInt(1, questionId);
        prepStmt.setInt(2, answerId);

        rowsAffected = prepStmt.executeUpdate();
        if (rowsAffected < 1) {
            throw new SQLException("Error while creating question-answer mapping");
        }
    }

    public static int getAnswerID(int questionID, String answer) throws SQLException {

        Connection connection = createConnection();
        try {
            String sqlString = GET_ANSWER_ID;
            PreparedStatement prepStmt = connection.prepareStatement(sqlString);
            prepStmt.setString(1, answer);
            prepStmt.setInt(2, questionID);

            ResultSet res = prepStmt.executeQuery();

            if (res.next()) {
                int id = res.getInt("id");
                return id;
            } else {
                throw new SQLException("Unable to retrieve answer id");
            }
        } finally {
            closeConnection(connection);
        }
    }

    public static Collection<Integer> getAnswerIDs(int questionID) throws SQLException {

        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_ANSWER_IDS);
            prepStmt.setInt(1, questionID);

            ResultSet res = prepStmt.executeQuery();

            Queue<Integer> queue = new ArrayDeque<Integer>();
            while(res.next()) {
                int id = res.getInt("id");
                queue.add(id);
            }

            if (queue.size() == 0) {
                throw new SQLException("Unable to retrieve answer ids");
            }
            return queue;
        } finally {
            closeConnection(connection);
        }
    }

    public static int countAnswer(int answerID) throws SQLException {

        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_ANSWER_COUNT);
            prepStmt.setInt(1, answerID);

            ResultSet res = prepStmt.executeQuery();

            int count = 0;
            if (res.next()) {
                count = res.getInt(1);
            }

            return count;
        } finally {
            closeConnection(connection);
        }
    }

    public static Collection<Integer> getQuestionIDs(int state, long currentTimeMillis) throws SQLException {
        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_QUESTIONS_BY_STATE_AND_FINISH_DATE);
            prepStmt.setInt(1, state);
            prepStmt.setLong(2, currentTimeMillis);

            ResultSet res = prepStmt.executeQuery();

            Queue<Integer> queue = new ArrayDeque<Integer>();
            while(res.next()) {
                int id = res.getInt("id");
                queue.add(id);
            }

            return queue;
        } finally {
            closeConnection(connection);
        }
    }

    public static Collection<Integer> getQuestionIDs(int state) throws SQLException {
        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_QUESTIONS_BY_STATE);
            prepStmt.setInt(1, state);

            ResultSet res = prepStmt.executeQuery();

            Queue<Integer> queue = new ArrayDeque<Integer>();
            while(res.next()) {
                int id = res.getInt("id");
                queue.add(id);
            }

            return queue;
        } finally {
            closeConnection(connection);
        }
    }

    public static Collection<Integer> getUserIDs(int answerId) throws SQLException {
        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_USER_IDS_BY_ANSWER_ID);
            prepStmt.setInt(1, answerId);

            ResultSet res = prepStmt.executeQuery();

            Queue<Integer> queue = new ArrayDeque<Integer>();
            while(res.next()) {
                int id = res.getInt("user_id");
                queue.add(id);
            }

            return queue;
        } finally {
            closeConnection(connection);
        }
    }

    public static int getUserRating(int userID) throws SQLException {

        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_USER_RATING_BY_ID);
            prepStmt.setInt(1, userID);

            ResultSet res = prepStmt.executeQuery();

            int rating = 0;
            if (res.next()) {
                rating = res.getInt(1);
            } else {
                throw new SQLException("Unable to retrieve rating");
            }

            return rating;
        } finally {
            closeConnection(connection);
        }
    }

    public static int getUserJOE() throws SQLException {

        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_USER_JOE_ID);

            ResultSet res = prepStmt.executeQuery();

            int id = 0;
            if (res.next()) {
                id = res.getInt(1);
            } else {
                addUser(Globals.JOE_LOGIN, Globals.JOE_PASS);
                prepStmt = connection.prepareStatement(GET_USER_JOE_ID);
                res = prepStmt.executeQuery();
                if (res.next()) {
                    id = res.getInt(1);
                } else {
                    throw new SQLException("Unable to retrieve JOE user");
                }
            }

            return id;
        } finally {
            closeConnection(connection);
        }
    }

    public static int getAuthorId(int questionId) throws SQLException {

        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_AUTHOR_ID_BY_QUESTION_ID);
            prepStmt.setInt(1, questionId);

            ResultSet res = prepStmt.executeQuery();

            int authorId = 0;
            if (res.next()) {
                authorId = res.getInt(1);
            } else {
                throw new SQLException("Unable to retrieve authorId");
            }

            return authorId;
        } finally {
            closeConnection(connection);
        }
    }

    public static long getQuestionFinishDate(int questionId) throws SQLException {

        Connection connection = createConnection();
        try {
            PreparedStatement prepStmt = connection.prepareStatement(GET_FINISH_DATE_BY_QUESTION_ID);
            prepStmt.setInt(1, questionId);

            ResultSet res = prepStmt.executeQuery();

            long finishDate = 0;
            if (res.next()) {
                finishDate = res.getLong(1);
            } else {
                throw new SQLException("Unable to retrieve finish date");
            }

            return finishDate;
        } finally {
            closeConnection(connection);
        }
    }

    public static void addAnswer(int userId, int answerID) throws SQLException {

        Connection connection = createConnection();
        try {
            String sqlString = INSERT_ANSWER;
            PreparedStatement prepStmt = connection.prepareStatement(sqlString);
            prepStmt.setInt(1, userId);
            prepStmt.setInt(2, answerID);

            int rowsAffected = prepStmt.executeUpdate();

            if (rowsAffected < 1) {
                throw new SQLException("Error while inserting answer");
            }
        } finally {
            closeConnection(connection);
        }
    }

    public static QuestionsGetterOutputFromDB getQuestions(QuestionsGetterInputForDB inputForDB) throws SQLException {

        Connection connection = createConnection();
        try {

            String sqlDropQuestions = DROP_TEMPRORARY_QUESSTIONS;
            PreparedStatement prepStmtDrop = connection.prepareStatement(sqlDropQuestions);
            prepStmtDrop.execute();
            prepStmtDrop.close();


            String sqlCreateQuestions = CREATE_TEMPRORARY_QUESSTIONS;
            PreparedStatement prepStmtCreate = connection.prepareStatement(sqlCreateQuestions);

            prepStmtCreate.setInt(1, inputForDB.userId);
            prepStmtCreate.setInt(2, inputForDB.questionsOffset);
            prepStmtCreate.setInt(3, inputForDB.questionNumber);

            prepStmtCreate.execute();
            prepStmtCreate.close();

            String sqlRequest = GET_QUESTIONS_WITHOUT_LOCATION;;
            PreparedStatement prepStmt = connection.prepareStatement(sqlRequest);



//            System.out.println(inputForDB.userId);
//            System.out.println(inputForDB.latitude);
//            System.out.println(inputForDB.longitude);
//            System.out.println(inputForDB.radius);
//            System.out.println(inputForDB.questionsOffset);
//            System.out.println(inputForDB.questionNumber);

            ResultSet resSet = prepStmt.executeQuery();
            return outputFromResultSet(resSet);
        } finally {
            closeConnection(connection);
        }
    }

    private static QuestionsGetterOutputFromDB outputFromResultSet(ResultSet resSet) throws SQLException {
        
        if (!resSet.next()) {
            // These nulls will be "nothing" in SOAP message so it's okay
            return new QuestionsGetterOutputFromDB(null, null, null, null, null);
        }

        String questionText = null;
        QuestionDB questionDB = null;
        Map<QuestionDB, ArrayList<QuestionOption>> questionsWithOptions = new HashMap<QuestionDB,
                ArrayList<QuestionOption>>();
        do {
            String newText = resSet.getString("text");
            if (false == newText.equals(questionText)) {
                questionText = newText;
                questionDB = new QuestionDB(newText, resSet.getInt("lifetime"),
                        resSet.getInt("question_id"));
                questionsWithOptions.put(questionDB, new ArrayList<QuestionOption>());
            }
            questionsWithOptions.get(questionDB).add(new QuestionOption(
                    resSet.getInt("answer_id"), resSet.getString("answer")));
            System.out.println(resSet.getInt("answer_id"));
            System.out.println(resSet.getString("answer"));

        } while (resSet.next());

        int size = questionsWithOptions.size();
        
        String[] questions = new String[size];
        String[][] answers = new String[size][];
        int [] lifetimes = new int[size];
        int [] questionIds = new int[size];
        int [][] answerIds = new int[size][];
        
        int arrayIndex = 0;
        for (QuestionDB question : questionsWithOptions.keySet()) {
            questions[arrayIndex] = question.questionText;
            lifetimes[arrayIndex] = question.lifetime;
            questionIds[arrayIndex] = question.id;

            ArrayList<QuestionOption> options = questionsWithOptions.get(question);
            answers[arrayIndex] = new String[options.size()];
            answerIds[arrayIndex] = new int[options.size()];

            int optionIndex = 0;
            for (QuestionOption option : options) {
                answers[arrayIndex][optionIndex] = option.text;
                answerIds[arrayIndex][optionIndex] = option.id;
                ++optionIndex;
            }
            ++arrayIndex;
        }

        return new QuestionsGetterOutputFromDB(questions, answers, lifetimes, answerIds, questionIds);
    }

    public static boolean checkAnswerID(int questionID, int answerId) throws SQLException {

        Connection connection = createConnection();
        try {
            String sqlString = GET_ANSWER_ID_AND_QUESTION_ID;
            PreparedStatement prepStmt = connection.prepareStatement(sqlString);
            prepStmt.setInt(1, answerId);
            prepStmt.setInt(2, questionID);

            ResultSet res = prepStmt.executeQuery();

            if (res.next()) {
                return true;
            } else {
                return false;
            }
        } finally {
            closeConnection(connection);
        }
    }

    public static QuestionsStatGetterOutputFromDB getQuestionsStat(QuestionsStatGetterInputForDB inputForDB) throws SQLException {
        Connection connection = createConnection();
        try {
            int userId = inputForDB.userId;
            int[] questionsIds = inputForDB.questionIds;
            
            String sqlQuestStat = GET_QUESTION_STAT;
            String sqlQuestIncome = GET_QUESTION_INCOME;

            PreparedStatement prepStmtQuestStat;
            PreparedStatement prepStmtQuestInc;

            ResultSet questInc;
            ResultSet questStat;
            
            PreparedStatement prepStmtAnsHits;
            ResultSet resHits;
            
            ArrayList<QuestionStatDB> questionsArray = new ArrayList<QuestionStatDB>();
            
            for (int questionId : questionsIds) {
                prepStmtQuestStat = connection.prepareStatement(sqlQuestStat);
                prepStmtQuestStat.setInt(1, questionId);
                System.out.println(questionId);
                questStat = prepStmtQuestStat.executeQuery();
                if (questStat.next()) {
                    prepStmtQuestInc = connection.prepareStatement(sqlQuestIncome);
                    prepStmtQuestInc.setInt(1, questionId);
                    prepStmtQuestInc.setInt(2, userId);
                    questInc = prepStmtQuestInc.executeQuery();

                    int income = 0;
                    if (questInc.next()) {
                        income = questInc.getInt("income");
                    }
                    ArrayList<Integer> answerIds = new ArrayList<Integer>();
                    ArrayList<String> answers = new ArrayList<String>();
                    ArrayList<Integer> answersHits = new ArrayList<Integer>();
                    QuestionStatDB questionStatDB = new QuestionStatDB(
                            questionId,
                            questStat.getInt("author_id"),
                            questStat.getString("text"),
                            questStat.getInt("lifetime"),
                            income,
                            answers,
                            answerIds,
                            answersHits);
                    questionsArray.add(questionStatDB);
                    do {
                        questionStatDB.answers.add(questStat.getString("answer"));
                        questionStatDB.answerIds.add(questStat.getInt("answer_id"));

                        prepStmtAnsHits = connection.prepareStatement(GET_ANSWER_COUNT);
                        prepStmtAnsHits.setInt(1, questStat.getInt("answer_id"));
                        resHits = prepStmtAnsHits.executeQuery();

                        int count = 0;
                        if (resHits.next()) {
                            count = resHits.getInt(1);
                        }
                        questionStatDB.answerHits.add(count);
                    } while (questStat.next());
                }
            }

            int size = questionsArray.size();

            String[] questions = new String[size];
            String[][] answers = new String[size][];
            int [] lifetimes = new int[size];
            int [] questionIds = new int[size];
            int [][] answerIds = new int[size][];
            int [][] answerHits = new int[size][];
            int [] incomes = new int[size];
            boolean [] doesBelong = new boolean[size];

            int arrayIndex = 0;
            for (QuestionStatDB question : questionsArray) {
                questions[arrayIndex] = question.questionText;
                lifetimes[arrayIndex] = question.lifetime;
                questionIds[arrayIndex] = question.id;
                incomes[arrayIndex] = question.income;
                doesBelong[arrayIndex] =  (userId == question.author_id) ? true : false;

                int answersNumber = question.answers.size();
                
                answers[arrayIndex] = new String[answersNumber];
                answerIds[arrayIndex] = new int[answersNumber];
                answerHits[arrayIndex] = new int[answersNumber];

                for (int index = 0; index < answersNumber; ++index) {
                    answers[arrayIndex][index] = question.answers.get(index);
                    answerIds[arrayIndex][index] = question.answerIds.get(index);
                    answerHits[arrayIndex][index] = question.answerHits.get(index);
                }
                arrayIndex++;
            }
            return new QuestionsStatGetterOutputFromDB(questionIds, questions, answerIds,
                    answers, lifetimes, incomes, doesBelong, answerHits);
        } finally {
            closeConnection(connection);
        }
    }

    /**
     * Method for updating questions and calculating results.
     */
    public static void updatePreCloseQuestions() throws SQLException {
        getUserJOE();

        Connection connection = createConnection();
        connection.setAutoCommit(false);
        try {
            Collection<Integer> questions = getQuestionIDs(0, System.currentTimeMillis());
            for (Integer questionID : questions) {
                try {
                    LinkedHashMap<Integer, Integer> countAnswer = new LinkedHashMap<Integer, Integer>();
                    Collection<Integer> answers = getAnswerIDs(questionID);
                    int dislikes = 0;
                    int allCount = 0;
                    for (Integer answerId : answers) {
                        int count = dislikes = countAnswer(answerId);
                        allCount += count;
                        countAnswer.put(answerId, count);
                        Collection<Integer> users = getUserIDs(answerId);
                        for (int userId : users) {
                            // insert to result
                            PreparedStatement statement = connection.prepareStatement(INSERT_RESULT, Statement.RETURN_GENERATED_KEYS);

                            statement.setInt(1, userId);
                            statement.setInt(2, questionID);
                            statement.setInt(3, count);
                            statement.executeUpdate();

                            // update user rating
                            int oldRating = getUserRating(userId);
                            statement = connection.prepareStatement(UPDATE_USER_RATING_BY_ID);
                            statement.setInt(1, oldRating + count);
                            statement.setInt(2, userId);
                            statement.executeUpdate();
                        }
                    }

                    // update question state
                    PreparedStatement statement = connection.prepareStatement(UPDATE_QUESTION_STATE_BY_ID);
                    statement.setInt(1, 2); // FIX ME: Need remove constant from code
                    statement.setInt(2, questionID);
                    statement.executeUpdate();

                    // update question lifetime
                    long lifetime = 0;
                    statement = connection.prepareStatement(UPDATE_QUESTION_LIFETIME_BY_ID);
                    statement.setLong(1, lifetime);
                    statement.setInt(2, questionID);
                    statement.executeUpdate();

                    if (allCount == 0) {
                        int joeId = getUserJOE();
                        Random random = new Random();
                        int randomAnswerIndex = random.nextInt() % answers.size();
                        int randomAnswerId = -1;
                        int i = 0;
                        for (Integer answerId : countAnswer.keySet()) {
                            if (i == randomAnswerIndex) {
                                randomAnswerId = answerId;
                                break;
                            }
                            i++;
                        }

                        if (randomAnswerId == -1) {
                            randomAnswerId = answers.iterator().next();
                        }
                        // add joe answer
                        //System.out.println("Answer id = " + randomAnswerId);
                        //System.out.println("Answer index = " + randomAnswerIndex);
                        //System.out.println("Answers = " + countAnswer.toString());
                        addAnswer(joeId, randomAnswerId);
                        if (randomAnswerIndex == answers.size() - 1) {
                            dislikes++;
                        } else {
                            allCount++;
                        }
                    }

                    // update author
                    int authorId = getAuthorId(questionID);
                    int oldRating = getUserRating(authorId);
                    statement = connection.prepareStatement(UPDATE_USER_RATING_BY_ID);
                    statement.setInt(1, oldRating + allCount - 2 * dislikes);
                    statement.setInt(2, authorId);
                    statement.executeUpdate();

                    // insert to result
                    statement = connection.prepareStatement(INSERT_RESULT);
                    statement.setInt(1, authorId);
                    statement.setInt(2, questionID);
                    statement.setInt(3, allCount - 2 * dislikes);
                    statement.executeUpdate();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            connection.commit();
        } finally {
            closeConnection(connection);
        }
    }

    /**
     * Method for updating lifetime.
     */
    public static void updateQuestions() throws SQLException {
        Connection connection = createConnection();
        connection.setAutoCommit(false);
        try {
            Collection<Integer> questions = getQuestionIDs(0);
            long currTimeMillis = System.currentTimeMillis();
            for (Integer questionID : questions) {
                long finishDate = getQuestionFinishDate(questionID);
                long lifetime = finishDate - currTimeMillis;
                if (lifetime < 0) {
                    lifetime = 0;
                }

                // update question lifetime
                PreparedStatement statement = connection.prepareStatement(UPDATE_QUESTION_LIFETIME_BY_ID);
                statement.setLong(1, lifetime);
                statement.setInt(2, questionID);
                statement.executeUpdate();
            }
            connection.commit();
        } finally {
            closeConnection(connection);
        }
    }

    public static boolean hasAnsweredBefore(int userID, int questionID) throws SQLException {
        Connection connection = createConnection();

        try {

            String sql = GET_USER_ANSWER;
            PreparedStatement prepStmt = connection.prepareStatement(sql);
            prepStmt.setInt(1, questionID);
            prepStmt.setInt(2, userID);
            ResultSet res = prepStmt.executeQuery();

            boolean isNext = res.next();
            return isNext;
        }
        finally {
            closeConnection(connection);
        }
    }

}
